<?php

namespace App\Service;

use App\Models\EwAds;
use App\Models\EwAdsEEvents;
use App\Models\EwAdsPos;
use Phalcon\Mvc\Model\Resultset\Simple as Resultset;
use Phalcon\Db;
use Phalcon\Mvc\Model\Transaction\Manager as TxManager;
use Phalcon\Mvc\Model\Transaction\Failed as TxFailed;
class AdsService extends BaseService
{
	const DISPLAY_TRUE = 1;		//显示标识：是
	const DISPLAY_FALSE = 0;	//显示标识：否
	const DEL_TRUE = 1;			//删除标识：是
	const DEL_FALSE = 0;		//删除标识：否

	// SELECT * FROM ew_ads AS a WHERE a.pos_id IN (SELECT  p.id FROM ew_ads_pos AS p WHERE p.`pos_sn` = "deco")
	public static function getList($params, $count = FALSE)
	{
		$info = EwAdsPos::find(['columns'=>'id','conditions'=>'pos_sn = "' . $params->pos_sn . '"'])->toArray();

		if ($info && $info[0] && $info[0]['id']){
			$pos_id = $info[0]['id'];
		}else{
			if ($count)
				return 0;
			return NULL;
		}

		$builder = new \Phalcon\Mvc\Model\Query\Builder();
		$builder->columns(['a.id,a.go_url,a.title,a.short_title,a.thumb,a.create_time']);
		$builder->addFrom("App\Models\EwAds", 'a');
		$builder->andWhere("a.is_del = " . self::DEL_FALSE);
		// $builder->inWhere("a.pos_id",'(SELECT p.id FROM App\Models\EwAdsPos AS p WHERE p.`pos_sn` = "deco")');
		$builder->limit($params->page_num,($params->p-1)*$params->page_num);

		//is_display
		if (isset($params->is_display) && $params->is_display )
			$builder->andwhere("a.is_display = " . $params->is_display);

		//pos_id
		if (isset($pos_id) && $pos_id > 0)
			$builder->andwhere("a.pos_id = " . $pos_id);

		//title
		if (isset($params->title) && $params->title ){
			$builder->andwhere("a.title LIKE '%" . $params->title . "%'");
		}

		if ($count){
			return $builder->getQuery()->execute()->count();
		}else{
			$builder->limit($params->page_num,($params->p-1)*$params->page_num);
			$result = $builder->getQuery();
			return $result->execute();
		}
	}

	public static function getSearch($params, $count = FALSE)
	{
		$query = EwAds::query();
		$query->columns(['id,title,short_title,thumb,over_time,is_display,is_del,pos_id']);
		$query->andWhere("is_del = " . self::DEL_FALSE);

		//is_display
		if (isset($params->is_display) && $params->is_display)
			$query->andwhere("is_display =" . $params->is_display);

		//pos_id
		if (isset($params->pos_id) && $params->pos_id)
			$query->andwhere("pos_id =" . $params->pos_id);
        if (isset($params->city_id) && $params->city_id)
            $query->andwhere("city_id =" . $params->city_id);

		//title
		if (isset($params->title) && $params->title )
			$query->andwhere("short_title LIKE '%" . $params->title . "%'");

		//short_title
		if (isset($params->short_title) && $params->short_title )
			$query->andwhere("short_title LIKE '%" . $params->short_title . "%'");

		//start_time end_time
		if (isset($params->start_time) && $params->start_time && isset($params->end_time) && $params->end_time )
			$query->andwhere("over_time BETWEEN '" . $params->start_time . "' AND '" . $params->end_time ."' " );


		//总条数
		if ($count)
			return $query->execute()->count();

		//order_by 排序
		if (isset($params->order_by)){
			$sql = parent::orderBy($params->order_by);
			$query->orderBy($sql);
		}

		//分页
		$query->limit($params->page_num,($params->p-1)*$params->page_num);

		return $query->execute();
	}

	public static function getInfo($id = 0)
	{
		$builder = new \Phalcon\Mvc\Model\Query\Builder();
		$builder->columns(['a.id,a.title,a.pos_id,p.name,a.over_time,a.is_display,a.is_del']);
		$builder->addFrom("App\Models\EwAds", 'a');
		$builder->leftJoin("App\Models\EwAdsPos", 'a.pos_id = p.id' , 'p');
		$builder->where("a.id = " . $id);

		$result = $builder->getQuery();
		$data = $result->execute()->toArray();

		if (is_array($data) && isset($data[0]))
			return $data[0];
		
		return EwAds::findFirst($id);
	}

	/**
	 * [getAds 返回广告为对应的第一条广告]
	 * @param  [type] $params [description]
	 * @return [type]         [description]
	 */
	public static function getAds($params)
	{
		$builder = new \Phalcon\Mvc\Model\Query\Builder();
		$builder->columns(['a.title,a.short_title,a.thumb,a.go_url,a.create_time']);
		$builder->addFrom("App\Models\EwAds", 'a');
		$builder->leftJoin("App\Models\EwAdsPos", 'a.pos_id = p.id' , 'p');
		$builder->where("p.pos_sn = '" . $params->pos_sn . "'");
		$builder->andWhere("a.is_del = " . self::DEL_FALSE);
		$builder->andWhere("a.is_display = " . self::DISPLAY_TRUE);
		$builder->andWhere("a.over_time >= " . date("y-m-d",time()));
		$result = $builder->getQuery();
		$data = $result->execute()->toArray();

		if (is_array($data) && isset($data[0]))
			return $data[0];
		return NULL;
	}

	public static function getModel($id = 0)
	{
		return EwAds::findFirst($id);
	}

	public static function count()
	{
		return EwAds::count();
	}

	/**
	 * [display 上架]
	 * @param  [type] $id [description]
	 * @return [type]     [description]
	 */
	public static function display($id)
	{
		if (isset($id) && $id)
			$data = EwAds::findFirst($id);

		if ($data){
			$data->is_display = $data->is_display ? self::DISPLAY_FALSE : self::DISPLAY_TRUE;
			return $data->save();
		}

		return NULL;
	}

	/**
	 * [delete 移除/恢复数据]
	 * @param  [type] $id [description]
	 * @return [type]     [description]
	 */
	public static function delete($id)
	{
		if (isset($id) && $id)
			$data = EwAds::findFirst($id);

		if ($data){
			$data->is_del = $data->is_del ? self::DEL_FALSE : self::DEL_TRUE;
			return $data->save();
		}

		return NULL;		
	}

	/**
	 * [delete 彻底删除数据]
	 * @param  [type] $id [description]
	 * @return [type]     [description]
	 */
	public static function forever($id)
	{
		if (isset($id) && $id)
			$row = EwAds::findFirst($id);

		if ($row)
			return $row->delete();

		return NULL;
	}

	/**
	 * [save 保存]
	 * @param  [type] $data [description]
	 * @return [type]       [description]
	 */
	public static function save($data)
	{
		$row = new EwAds;
		if (isset($data->id) && $data->id)
			$row = EwAds::findFirst($data->id);

		return $row->save((array)$data);
	}

    /**
     * [getSpeAdsList 列表]
     * @param  [type] $params [description]
     * @return [type]         [description]
     */
    public static function getSpeAdsList($params)
    {
        date_default_timezone_set('Asia/Chongqing');
        $builder = new \Phalcon\Mvc\Model\Query\Builder();
        $builder->columns(['a.id,a.title,a.image_withurl as images']);
        $builder->addFrom("App\Models\EwAds", 'a');
        $builder->andWhere("a.is_del = " . self::DEL_FALSE);
        // $builder->inWhere("a.pos_id",'(SELECT p.id FROM App\Models\EwAdsPos AS p WHERE p.`pos_sn` = "deco")');
        $builder->limit(1);

        $pos_id=$params['type'];
        //pos_id
        if (isset($pos_id) && $pos_id > 0)
            $builder->andwhere("a.pos_id = " . $pos_id);
        //$curHour=intval(substr(date("Y-m-d H:i:s"),11,2));
        if(array_key_exists("city_id",$params)){
            $city_id=$params["city_id"];
            if($city_id!=''){
                $builder->andWhere("a.city_id in (" . $city_id . " , 0 )");
            }else{
				$builder->andWhere("a.city_id = " . $city_id);            
			}
        }

        $curDate=date('Y-m-d H:i:s');
        $curHour=intval(substr($curDate,11,2));
        $curDate=substr($curDate,0,10);
        //title
        $builder->andwhere("  '{$curDate}'>=substr(a.date_start,1,10) and '{$curDate}'<=substr(a.over_time,1,10) and  {$curHour}>=CAST(substr(a.time_start,1,2) AS int) and  {$curHour}<=CAST(substr(a.time_end,1,2) AS int)  ");

        $result = $builder->getQuery();
        //echo  $builder->getPhql();
        return $result->execute()->toArray();



        $advquery=" SELECT ad.id,ad.image_withurl from ew_ads ad  where ad.pos_id={$params['type']} and  ad.is_del='0' and CURRENT_TIMESTAMP()>=ad.date_start and CURRENT_TIMESTAMP()<=ad.over_time";
        $robot = new \App\Models\Robots();

        $result=$robot->getReadConnection()->query($advquery);
        $result->setFetchMode(Db::FETCH_ASSOC);
        $list = $result->fetchAll();

        return array('advlist'=>$list);
    }
    /**
     * [checkExistAdvBeTiem  检测在指定的时间段内是否已经存在某广告]
     * @param  [type] $params [description]
     * @return [type]         [description]
     */
    public static function checkExistAdvBeTiem($params){
        $builder = new \Phalcon\Mvc\Model\Query\Builder();
        $builder->columns(['a.id,a.title,a.date_start,a.over_time,a.time_start,a.time_end']);
        $builder->addFrom("App\Models\EwAds", 'a');
        $builder->andWhere("a.is_del = " . self::DEL_FALSE);
        // $builder->inWhere("a.pos_id",'(SELECT p.id FROM App\Models\EwAdsPos AS p WHERE p.`pos_sn` = "deco")');

        $pos_id=$params['type'];
        //pos_id
        if (isset($pos_id) && $pos_id > 0)
            $builder->andwhere("a.pos_id = " . $pos_id);
        $advbegindate=$params['date_start']." ".$params['time_start'].":00";
        $evertime=intval($params['time_start']);
        $evertimeend=intval($params['time_end']);
        //  and ({$evertime}>CONVERT(a.time_start,SIGNED) and {$evertime}<=CONVERT(a.time_end,SIGNED)
        $builder->andwhere("  '{$advbegindate}'>a.date_start and '{$advbegindate}'<a.over_time ");
        if(array_key_exists("city_id",$params) && $params['city_id']!=''){
            $builder->andWhere("a.city_id = " . $params['city_id']);
        }


        $builder->andwhere("  ($evertimeend>CAST(a.time_start AS int) and $evertimeend<CAST(a.time_end AS int)) or
                    ($evertime>CAST(a.time_start AS int) and $evertime<CAST(a.time_end AS int)) or ($evertime<=CAST(a.time_start AS int) and $evertimeend>=CAST(a.time_end AS int))  " );
        //$builder->orWhere("  $evertime>CAST(a.time_start AS int) and $evertime<=CAST(a.time_start AS int) " );
        //$builder->orWhere("  CAST(a.time_end AS int)<=$evertime and CAST(a.time_end AS int)>=$evertimeend " );
        //$builder->betweenWhere("  CAST(a.time_end AS int),$evertime,$evertimeend" );

        $result = $builder->getQuery();

        return $result->execute()->toArray();
    }
    /**
     * [save 保存]
     * @param  [type] $data [description]
     * @return [type]       [description]
     */
    public static function saveEventsReg($data)
    {

        $msg='ok';
        try {
            foreach($data as $key=>$record){
                $row = new EwAdsEEvents;

                if($row->save($record)===false)
                {
                    $msg='error';
                    break;
                }
            }
        } catch (TxFailed $e) {
            return $e->getMessage();
        }
        return $msg;

        try {
            $txManager = new TxManager();

            $transaction = $txManager->get();
            $sussful=true;
            foreach($data as $key=>$record){
                $row = new EwAdsEEvents;
                $row->setTransaction($transaction);

                if($row->save($record)===false)
                {
                    $sussful=false;
                    $transaction->rollback("Can't save robot");
                    break;
                }
            }
            if($sussful){
                $transaction->commit();
                return 'ok';
            }else{
                return 'error';
            }


        } catch (TxFailed $e) {
            return $e->getMessage();
        }
        //$row = new EwAdsEEvents;
        //return $row->save($data);
    }
    /**
     * [checkExistAdvBeTiem  检测在指定的时间段内是否已经存在某广告]
     * @param  [type] $params [description]
     * @return [type]         [description]
     */
    public static function isActivyEvents($params){
        if(array_key_exists("team_id",$params) && $params['team_id']!=''){
            $phonefield=",a.mobliephone";
        }else{
            $phonefield="";
        }
        $builder = new \Phalcon\Mvc\Model\Query\Builder();

        $builder->columns(['a.activys,a.team_id,a.id,a.invcode,a.shopname,a.name'.$phonefield]);
        $builder->addFrom("App\Models\EwAdsEEvents", 'a');
        if( array_key_exists("mobliephone",$params) && $params['mobliephone']!=''){
            $builder->andWhere(" a.mobliephone ='{$params['mobliephone']}' ");
        }elseif(array_key_exists("invcode",$params) && $params['invcode']!=''){
            $builder->andWhere(" lower(a.invcode) ='{$params['invcode']}' ");
        }elseif(array_key_exists("team_id",$params) && $params['team_id']!=''){
            $builder->andWhere(" a.team_id ='{$params['team_id']}' ");
        }
        if(array_key_exists("activys",$params) && $params['activys']!=''){
            $builder->andWhere(" a.activys ='{$params['activys']}' ");
        }

        $result = $builder->getQuery();
        //echo  $builder->getPhql();
        return $result->execute()->toArray();

    }

    public static function memberActivyStatus($data)
    {
        $row = new EwAdsEEvents;
        if (isset($data['id']) && $data['id'])
            $row = EwAdsEEvents::findFirst($data['id']);

        return $row->save($data);
    }
    public static function getJoinTeamInfo($config){

        $connection= self::getDi()->getShared('db');

        $result = $connection->query("SELECT * from (SELECT COUNT(e.activys) actno,e.team_id,e.shopname from ew_eventsreg e where 1  GROUP BY e.team_id ) temp where temp.actno>=1");

        $result->setFetchMode(Db::FETCH_ASSOC);

        //echo $connection->getSQLStatement(); // sql

        $data = $result->fetchAll();

        return $data;


        $advquery=" SELECT * from (SELECT COUNT(e.activys) actno,e.team_id,e.shopname from ew_eventsreg e where 1  GROUP BY e.team_id ) temp where temp.actno>=5";
        $robot = new \App\Models\Robots($di);


        $result=$robot->getReadConnection()->query($advquery);
        $result->setFetchMode(Db::FETCH_ASSOC);
        $list = $result->fetchAll();
        return $list;
    }
}